//
//  CHSqliteManage.m
//  MySqliteDemo
//
//  Created by cyptt on 2022/8/17.
//

#import "CHSqlite3.h"
#import <sqlite3.h>


@interface CHSqlite3()

@end
@implementation CHSqlite3
static CHSqlite3 * _manage= nil;
sqlite3 * db;
+(instancetype)shareInstance
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        if (_manage == nil) {
            _manage = [[self alloc]init];
        
        }
    });
    return _manage;
}



-(BOOL)createDataBase:(NSString *)dbName{

    NSString * sqName = [[self path]stringByAppendingFormat:@"/%@",dbName];
    int dataBaseResult = sqlite3_open([sqName UTF8String], &db);
    if (dataBaseResult == SQLITE_OK) {
        NSLog(@"创建了数据库成功");

        return YES;
    }else{
        NSLog(@"创建了数据库失败");
        return NO;
      
    }
}


-(BOOL)createTable:(NSString *)tabName paramSql:(NSString *)paramSql{
    char *error = NULL;
    NSString * tabKey =[NSString stringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement,",tabName] ;
    NSString * sqlString = [NSString stringWithFormat:@"%@%@)",tabKey,paramSql];
    
    NSLog(@"sql===%@",sqlString);
    const char *createSQL = [sqlString UTF8String];
    
    // 执行sql语句
    /**
     第1个参数：数据库对象
     第2个参数：sql语句
     第3个参数：查询时候用到的一个结果集闭包
     第4个参数：用不到
     第5个参数：错误信息
     */
    int tableResult = sqlite3_exec(db, createSQL, NULL, NULL, &error);
    
    if (tableResult == SQLITE_OK) {
        NSLog(@"创建表成功");
        return  YES;
    }else{
        NSLog(@"创建表失败:%s",error);
        return  NO;
    }
}

// 插入数据
- (void)insertTable:(NSString *)tabName paramDic:(NSDictionary *)paramDic
{
 
    NSMutableArray * keyArrs = [NSMutableArray array];
    NSMutableArray * valueArrs = [NSMutableArray array];
    for (NSString * key in [paramDic allKeys]) {
        [keyArrs addObject:key];
        NSString * valueString = [NSString stringWithFormat:@"'%@'",paramDic[key]];
        [valueArrs addObject:valueString];
    }
    
    NSString * keys = [keyArrs componentsJoinedByString:@","];
    NSString * values = [valueArrs componentsJoinedByString:@","];
   
    //  sql 语句
    NSString * sql = [NSString stringWithFormat:@"insert into %@ (%@) values (%@)",tabName,keys,values];

    NSLog(@"sql===%@",sql);
    
    // 执行 sql 语句
    char *errMsg = NULL;
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, &errMsg);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功 -");
    } else {
        NSLog(@"插入数据失败 - %s",errMsg);
    }
}

-(nullable NSArray*)queryAll:(NSString *)tabName{


    NSString * sqlStr = [NSString stringWithFormat:@"SELECT * FROM %@",tabName];
    
    return [self queryConditionSql:sqlStr];
}

-(nullable NSArray*)queryConditionSql:(NSString *)Sql{

    NSString * sqlStr = Sql;
    const char * sql=sqlStr.UTF8String;
    sqlite3_stmt *stmt = NULL;
  
    NSMutableArray * resultArr = [NSMutableArray array];
   
    dispatch_queue_t queue = dispatch_queue_create("ch.com.mymb", NULL);
 
    NSLog(@"00000001");
    
    //异步情况暂时未用
//    dispatch_async(queue, ^{
//
//    });
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {   //
        int columnCount = sqlite3_column_count(stmt);
      
        // 每调用一次sqlite3_step函数，stmt就会指向下一条记录
        while (sqlite3_step(stmt) == SQLITE_ROW) {  // 找到一条记录

            
            NSMutableDictionary * sondic = [NSMutableDictionary dictionary];
            for (int i = 0;i<columnCount;i++){
           
                if (sqlite3_column_type(stmt, i) == SQLITE_NULL) {
                    const unsigned char * value = sqlite3_column_text(stmt, i);
                    const char * key = sqlite3_column_name(stmt, i);
                
//                    NSString * valueOC = [NSString stringWithUTF8String:(char *)value];
                    NSString * keyOC = [NSString stringWithUTF8String:key];
                
                    [sondic setValue:NULL forKey:keyOC];
                 
                
                }
                if (sqlite3_column_type(stmt, i) == SQLITE_TEXT) {
               
                    const unsigned char * value = sqlite3_column_text(stmt, i);
                    const  char * key = sqlite3_column_name(stmt, i);
                   
                    NSString * valueOC = [NSString stringWithUTF8String:(char *)value];
                    NSString * keyOC = [NSString stringWithUTF8String:key];
                
                    [sondic setValue:valueOC forKey:keyOC];
                }
                if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER) {
              
                    int value = sqlite3_column_int(stmt, i);
                    const  char * key = sqlite3_column_name(stmt, i);
                
           
                    NSString * keyOC = [NSString stringWithUTF8String:key];
                
                    [sondic setValue:@(value) forKey:keyOC];
                }
                if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT) {
               
                    double value = sqlite3_column_double(stmt, i);
                    const  char * key = sqlite3_column_name(stmt, i);
                  
                  
                    NSString * keyOC = [NSString stringWithUTF8String:key];
                
                    [sondic setValue:@(value) forKey:keyOC];
                }
            }
            [resultArr addObject:sondic];
          
        }
        
        
        NSLog(@"00000002");
    } else {
        sqlite3_finalize(stmt);
        NSLog(@"查询语句有问题");
        
    }
    NSLog(@"00000003");
    return resultArr;
}

//修改数据
-(BOOL)updateSql:(NSString *)sql{
    
    //其实Sqlite的数据插入，修改，删除执行的方法都是一样的只是执行的sql语句不一样，想知道sql的更多语句操作自行百度了，比较多这里就不讲解了，只介绍一些基本的操作方法。
    //sqlite3数据(把年龄大于60的学生名字全部改成‘哈哈’)
    char *errorMesg = NULL;
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, &errorMesg);
    if (result == SQLITE_OK) {
        
        NSLog(@"更改成功");
        return YES;
    }else {
        NSLog(@"更改失败");
        return NO;
    }
    //然后执行查询语句就能看到更改后的效果了
}

//删除数据
-(BOOL)deleteSql:(NSString *)sql{

    char *errorMesg = NULL;
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, &errorMesg);
    if (result == SQLITE_OK) {

        NSLog(@"删除成功");
        return YES;
    }else {
        NSLog(@"删除失败");
        return NO;
    }
}

-(BOOL)close{
    int  rc;
    BOOL retry;
    BOOL triedFinalizingOpenStatements = NO;
    
    do {
        retry   = NO;
        rc      = sqlite3_close(db);
        if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
            if (!triedFinalizingOpenStatements) {
                triedFinalizingOpenStatements = YES;
                sqlite3_stmt *pStmt;
                while ((pStmt = sqlite3_next_stmt(db, nil)) !=0) {
                    NSLog(@"Closing leaked statement");
                    sqlite3_finalize(pStmt);
                    retry = YES;
                }
            }
        }
        else if (SQLITE_OK != rc) {
            NSLog(@"error closing!: %d", rc);
        }
    }
    while (retry);

    return YES;
}

-(NSString * )path{
    NSString * cachePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject ;
    NSString * path = [cachePath stringByAppendingFormat:@"/chAppDb"];
    
    NSFileManager * fileManager = [NSFileManager defaultManager];
    BOOL isSucess = YES;
    BOOL isExist = [fileManager fileExistsAtPath:path];
    if (isExist ==NO) {
        NSError * error;
        if (![fileManager createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:&error]) {
            isSucess =NO;
        }
    }
    
    return  path;
}

@end
